using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Drawing.Printing;
using System.Windows.Forms;
using System.Configuration;
using System.Collections;
using PrintDataGrid;
using System.Collections.Specialized;

namespace eBirdAttend
{
    public partial class MonthlyRpt : UserControl
    {

        public string strCon = Properties.Settings.Default["ConString"].ToString();
        SqlConnection sqlCon = new SqlConnection();
        SqlCommand sqlCmd = new SqlCommand();
        DataGridViewPrinter MyDataGridViewPrinter;

        public MonthlyRpt()
        {
            InitializeComponent();
            sqlCon.ConnectionString = strCon;
            sqlCmd.Connection = sqlCon;
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            
            string strWhere="";
            string _monthValue = dateStartDate.Value.Month.ToString();
            string _yearValue = dateEndDate.Value.Year.ToString();
            string strStartDate = dateStartDate.Value.Date.ToString("MM/dd/yyyy");
            string strEndDate = dateEndDate.Value.Date.ToString("MM/dd/yyyy");
            //MessageBox.Show(strStartDate);

            int dateStatus = -1;
            //if (cmbRange.SelectedIndex == 0)
            //{
            //    MessageBox.Show("Select the date condtion");
            //    cmbRange.Focus();
            //    return;
            //}
            //else
            //{
            //    if (cmbRange.SelectedIndex == 1)
            //    {
            //        dateStatus = 2; //between
            //        strWhere = "Convert(varchar,AttnDate,101) >= '" +strStartDate.ToString()+ "' and AttnDate<='" +strEndDate.ToString()+"'";
            //    }
            //    else if (cmbRange.SelectedIndex == 2)
            //    {
            //        dateStatus = 3; //less than equal to
            //        strWhere = "Convert(varchar,AttnDate,101) <= '" + strStartDate.ToString() + "' ";
            //    }
            //    else if (cmbRange.SelectedIndex == 3)
            //    {
            //        dateStatus = 4; //greater than equal to
            //        strWhere = "Convert(varchar,AttnDate,101) >= '" + strStartDate.ToString()+"' ";
            //    }
            //}
            //if (cmbBranch.SelectedIndex != 0)
            //{
            //    strWhere = strWhere + " and EmpBrID = " + cmbBranch.SelectedValue.ToString();
            //}

            strWhere = "Month(AttnDate) = '" + _monthValue.ToString() + "' and YEAR(AttnDate) = '"+_yearValue.ToString()+"'";

            if (cmbDept.SelectedIndex != 0)
            {
                strWhere = strWhere + " and EmpDeptID = " + cmbDept.SelectedValue.ToString();

            }
            if (cmbEmployee.SelectedIndex != 0)
            {
                strWhere = strWhere + " and EmpId = " + cmbEmployee.SelectedValue.ToString();
            }
            //MessageBox.Show(strWhere.ToString());
            sqlCon.Open();
            //sqlCmd.CommandText = "SELECT     EmpFirstName AS FirstName, EmpLastName AS LastName, ShName AS Shift, AttnDate AS Date, PresentText AS EmpStatus, Description AS DayStatus,"
            //          + "dtHours AS [HoursWorked], dtMinutes AS [Worked(Min)], DepName AS Department, BrName AS Branch, dtOverTimeMin AS OverTime,"
            //          + "dtLatenessMin AS Lateness, dtFirstInTime AS FirstInTime, dtLastOutTime AS LastOutTime"
            //          + " FROM AttendanceReportView ";
            sqlCmd.CommandText = "SELECT     EmpFirstName +' '+ EmpLastName AS Name, ShName AS Shift, AttnDate AS Date, PresentText AS EmpStatus, Description AS DayStatus,"
                     + "dtHours AS [HoursWorked], dtMinutes AS [Worked(Min)], [OverTime(Hrs:Min)],"
                     + "[Lateness(Hrs:Min)], dtFirstInTime AS FirstInTime, dtLastOutTime AS LastOutTime"
                     + " FROM AttendanceReportView ";

            if (strWhere != "")
            {
                strWhere = "where " + strWhere;
                sqlCmd.CommandText += strWhere;
            }
            //MessageBox.Show(sqlCmd.CommandText);
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Parameters.Clear();            
            sqlCmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
            sqlAdap.Fill(ds);
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = ds.Tables[0].ToString();
            dataGridView1.Columns[0].MinimumWidth = 116;
             sqlCon.Close();            
        }

        private void btnShowCrystalReport_Click(object sender, EventArgs e)
        {
            //ShowCrsytalReport _ShowCrsytalReport = new ShowCrsytalReport();
            //_ShowCrsytalReport.strReportName = "Atnd";
            //_ShowCrsytalReport.Show();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void MonthlyRpt_Load(object sender, EventArgs e)
        {
           //ConfigurationManager.AppSettings.Add("AttendanceReport","c:\\AttendanceReport.xls");
            try
            {
               
                cmbGroup.SelectedIndex = 0;
                //cmbOrderBy.SelectedIndex = 0;
                departmentDataset.Clear();
                departmentTableAdapter1.Fill(departmentDataset.Department);

                DataTable table1 = new DataTable("Table1");

                table1 = departmentDataset.Tables[0].Clone();

                // Add some rows.
                DataRow row;
                row = table1.NewRow();
                row["depid"] = 0;
                row["depname"] = "[Select]";
                row["depcmpid"] = "1";
                row["dephod"] = "Select";
                table1.Rows.Add(row);

                // Accept changes.
                table1.AcceptChanges();

                departmentDataset.Tables[0].Constraints.Clear();
                DataTable table2 = departmentDataset.Tables[0];

                table1.Merge(table2);

                cmbDept.DataSource = table1;
                cmbDept.DisplayMember = "depname";
                cmbDept.ValueMember = "depid";

                branchTableAdapter1.Fill(branchDataset.Branch);
                table1 = new DataTable("Table1");

                table1 = branchDataset.Tables[0].Clone();

                // Add some rows.
                row = table1.NewRow();
                row["brbranchid"] = 0;
                row["brcmpid"] = 0;
                row["brname"] = "[Select]";

                table1.Rows.Add(row);

                // Accept changes.
                table1.AcceptChanges();

                branchDataset.Tables[0].Constraints.Clear();
                table2 = branchDataset.Tables[0];
                table1.Merge(table2);

                cmbBranch.DataSource = table1;
                cmbBranch.DisplayMember = "brname";
                cmbBranch.ValueMember = "brbranchid";

                //cmbRange.SelectedIndex = 0;
                cmbDept.SelectedIndex = 0;
                cmbBranch.SelectedIndex = 0;
                cmbBranch.Focus();

                employDetailsTableAdapter.Fill(employeeDataset1.EmployDetails);
                table1 = new DataTable("Table1");
                table1 = employeeDataset1.Tables[0].Clone();
                DataRow row1;
                row1 = table1.NewRow();
                row1["empid"] = 0;
                row1["empfirstname"] = "[Select]";
                table1.Rows.Add(row1);

                // Accept changes.
                table1.AcceptChanges();

                employeeDataset1.Tables[0].Constraints.Clear();
                table2 = employeeDataset1.Tables[0];

                table1.Merge(table2);

                cmbEmployee.DataSource = table1;
                cmbEmployee.DisplayMember = "empfirstname";
                cmbEmployee.ValueMember = "empid";
                cmbEmployee.SelectedIndex = 0;
                
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message.ToString());

            }
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            PrintDGV printdgv = new PrintDGV();
            printdgv.Print_DataGridView(dataGridView1);
        }

        private void export_Click(object sender, EventArgs e)
        {
            int j = 1;
            int i;
            string strReportTemplate = Application.StartupPath.ToString() + "\\" +
                 Properties.Settings.Default.MonthlyReport.ToString();

            if (strReportTemplate == "")
            {
                MessageBox.Show("Report Template Name Missing");
                return;
            }
            
            //ConfigurationManager.AppSettings["AttendanceReport"].ToString();
            //ConfigurationSettings.AppSettings["AttendanceReport"].ToString();
            
            StringCollection strHeaderCol = new StringCollection();
            EbExcelAuto exelAttnReport = new EbExcelAuto();
            string curColText = "";
            exelAttnReport.Init();
            exelAttnReport.OpenExcelFile(strReportTemplate);
            exelAttnReport.FillExcellSheetFromDatagrid("#START", dataGridView1, "Date");
            exelAttnReport.SaveAs(null);
            exelAttnReport.Close();
            /*
            if (!exelAttnReport.MoveCurPosToText("#START", 1, 1, 100, 100))
                MessageBox.Show("Pretext 'START' stored in the Hidden Row to indicate the start of Data area is missing.\r\n");
            ++exelAttnReport._CurRow;
            while(j<50)
            { 
                if (!exelAttnReport.GetColumnText(exelAttnReport._CurRow, j, ref curColText)) break;
                if(curColText=="") break;
                MessageBox.Show(curColText);
                strHeaderCol.Add(curColText);
                ++j;
            }
            ++exelAttnReport._CurRow;
            for (i= 0; i < dataGridView1.RowCount; ++i)
            {
                for (j = 0; j < dataGridView1.ColumnCount; ++j)
                {
                    
                    int iColIndex=strHeaderCol.IndexOf(dataGridView1.Columns[j].HeaderText);
                    if (iColIndex !=-1)

                        // MessageBox.Show(dataGridView1.Rows[i].Cells[j].Value.ToString());
                        if (dataGridView1.Rows[i].Cells[iColIndex].Value != null)
                        {
                            exelAttnReport._CurCol = iColIndex + 1;
                            exelAttnReport.SetValue(dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }

                        else
                        {
                            string strMsg = "Not matching =" + dataGridView1.Columns[j].HeaderText;
                            //strMsg=strMsg+","+strHeaderCol[j].ToString();
                            // MessageBox.Show(strMsg);
                        }
                }
                ++exelAttnReport._CurRow;
            }
           
         */   

        }

        private void fillBy1ToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                //this.employDetailsTableAdapter.FillBy1(this.employeeDataset1.EmployDetails);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void fillBy2ToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                //this.employDetailsTableAdapter.FillBy2(this.employeeDataset1.EmployDetails);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

    }
}
